use anyhow::anyhow;
use anyhow::ensure;
use anyhow::Result;
use chrono::Local;
use chrono::Utc;
use log::debug;
use log::info;
use log::trace;
use serde::Deserialize;
use serde::Serialize;
use surrealdb::opt::PatchOp;
use surrealdb::sql::Datetime;
use surrealdb::sql::Thing;
use yata::core::Candle;
use chrono::DateTime;

use crate::model;
use crate::model::indicators::trend_line::CrossTrendLineIndicator;
use crate::model::indicators::trend_line::Source;
use crate::model::indicators::INDICATOR_GROUP_KEYS;
use crate::model::kline::Kline;
use crate::model::kline::KlineIndicator;
use crate::model::kline::KlineMaline;
use crate::model::kline::KlineStat;
use crate::model::kline::Klt;
use crate::model::kline_flat::KlineFlat;
use crate::model::kline_flat::TrendLineResult;
use crate::model::matrix::compute_indicator_item_correlation_matrix;
use crate::model::matrix::extract_indicator_item_names;
use crate::model::matrix::CorrelationMatrix;
use crate::model::matrix::IndicatorComb;
use crate::model::security::Market;
use crate::model::security::SecurityType;
use crate::model::table::KLINE_FLAT_TABLE;
use crate::model::tick::Tick;
use crate::model::FudaRecord;

use super::FUDB;
use super::FUHLC;

impl Into<Candle> for Kline {
    fn into(self) -> Candle {
        Candle {
            open: self.open,
            high: self.high,
            low: self.low,
            close: self.close,
            volume: self.volume as f64,
        }
    }
}

impl Into<Candle> for &Kline {
    fn into(self) -> Candle {
        Candle {
            open: self.open,
            high: self.high,
            low: self.low,
            close: self.close,
            volume: self.volume as f64,
        }
    }
}

#[derive(Debug, Serialize, Deserialize, Clone)]
struct KlineV {
    id: Thing,
    vl: Kline,
}
pub async fn upsert_all(klines: &Vec<Kline>) -> Result<u32> {
    let start = Utc::now();

    let query = FUDB.query(
        r#"
        FOR $v IN $klines { 
            INSERT INTO kline  (id,security_id,klt,klts, open,high,low,close,volume,value,amplitude,frate,famount,trate, last_updated) 
            VALUES ($v.id,$v.security_id,$v.klt,$v.klts, $v.open, $v.high, $v.low, $v.close, $v.volume,  $v.value, $v.amplitude, $v.frate, $v.famount, $v.trate,$v.last_updated)
            ON DUPLICATE KEY UPDATE
                security_id=$v.security_id, 
                security_id=$v.security_id,
                klt=$v.klt,
                klts=$v.klts, 
                open=$v.open, 
                high=$v.high, 
                low=$v.low, 
                close=$v.close, 
                volume=$v.volume,  
                value=$v.value, 
                amplitude=$v.amplitude, 
                frate=$v.frate, 
                famount=$v.famount, 
                trate=$v.trate,
                last_update=$v.last_update;
        }
        "#
    ).bind(("klines", &klines));

    // debug!(
    //     "(kline::upsert_all) len={}, {:?} \n",
    //     kline_vs.len(),
    //     kline_vs
    // );
    let _rs = query.await?;
    let end = Utc::now();
    debug!("(kline::upsert_all) rs= {:?} spend time={:?}", _rs, end - start);

    Ok(1)
}

pub async fn upsert_all_klines_stat(klsums: &Vec<KlineStat>) -> Result<()> {
    let start_time = Utc::now();
    // let last_updated = format!("{}", FUHLC.new_timestamp());
    let query = FUDB.query(
        r#"
        FOR $v IN $klsums { 
            INSERT INTO kline_stat (id,kline_id,klts,security_id,market,security_type,ohlcv,smpr,upstop_counts,avg_ohlcv,highest,lowest) 
            VALUES ($v.id, $v.kline_id, $v.klts, $v.security_id, $v.market, $v.security_type, $v.ohlcv, $v.smpr, $v.upstop_counts, $v.avg_ohlcv, $v.highest, $v.lowest)
            ON DUPLICATE KEY UPDATE
                kline_id=$v.kline_id, 
                klts=$v.klts, 
                security_id=$v.security_id, 
                market=$v.market, 
                security_type=$v.security_type, 
                ohlcv=$v.ohlcv,
                smpr=$v.smpr,
                upstop_counts=$v.upstop_counts,
                avg_ohlcv=$v.avg_ohlcv, 
                highest=$v.highest, 
                lowest=$v.lowest ;
        }
        "#
    ).bind(("klsums", klsums));

    let _rs = query.await?;

    let spent_time = Local::now().signed_duration_since(start_time);
    trace!(
        "upsert_all_klines_summary  spent seconds={}.{} ",
        spent_time.num_seconds(),
        spent_time.num_milliseconds() % 1000
    );

    Ok(())
}
///
/// 由于历史复权数据可能随着当前分红配股的变化而变化，获取到后就更新本地的SecurityKline
///
/// security: &Security, the kline owner is Security
///
/// kline: &SecurityKline ,
///
pub async fn upsert(security_id: &str, kline: &Kline) -> Result<Option<FudaRecord>> {
    let id = Kline::id_from(security_id, &kline.klt, &kline.klts);

    let timestamp = match &kline.last_updated {
        Some(ts) => ts.clone(),
        None => format!("{}", FUHLC.new_timestamp()),
    };
    // Update a record with a specific ID
    let mut patch = FUDB.update(("kline", id)).patch(PatchOp::replace("/last_updated", timestamp));

    patch = patch.patch(PatchOp::replace("/security_id", security_id));
    patch = patch.patch(PatchOp::replace("/klt", &kline.klt));
    patch = patch.patch(PatchOp::replace("/klts", Datetime(kline.klts.clone())));
    patch = patch.patch(PatchOp::replace("/open", kline.open));
    patch = patch.patch(PatchOp::replace("/high", kline.high));
    patch = patch.patch(PatchOp::replace("/low", kline.low));
    patch = patch.patch(PatchOp::replace("/close", kline.close));
    patch = patch.patch(PatchOp::replace("/volume", kline.volume));
    patch = patch.patch(PatchOp::replace("/value", kline.value));
    patch = patch.patch(PatchOp::replace("/amplitude", kline.amplitude));
    patch = patch.patch(PatchOp::replace("/frate", kline.frate));
    patch = patch.patch(PatchOp::replace("/famount", kline.famount));
    patch = patch.patch(PatchOp::replace("/trate", kline.trate));

    let updated: Option<FudaRecord> = patch.await.map_err(|err| anyhow!(err))?;

    Ok(updated)
}

///
/// find trade_bot by it's id , e.g. z755uu11dfzxqpictna3
///
pub async fn find(kline_id: &str) -> Result<Kline> {
    let q_str = format!("SELECT * FROM $id ");

    let query = FUDB.query(&q_str).bind(("id", Thing::from(("kline", kline_id))));

    // execute query
    let mut response = query.await?;

    // println!("\nresponse= {:?}\n", response);

    let values: Vec<Kline> = response.take(0)?;

    if values.len() > 0 {
        return Ok(values[0].clone());
    }

    Err(anyhow!("trade_bot not found"))
}

pub async fn find_max_last_updated(security_id: &str) -> Result<String> {
    let klines = select(security_id, None, None, Some("last_updated DESC"), 0, 1).await?;
    match klines.first() {
        Some(s) =>
            match &s.last_updated {
                Some(v) => Ok(v.to_string()),
                None => Ok("".to_string()),
            }
        None => Ok("".to_string()),
    }
}

pub async fn find_latest_kline(security_id: &str, klt: &Klt) -> Result<Option<Kline>> {
    let klines = select(security_id, None, Some(klt), Some("klts DESC"), 0, 1).await?;
    match klines.first() {
        Some(kl) => Ok(Some(kl.clone())),
        None => Ok(None),
    }
}

pub async fn find_lowest_klines(
    security_id: &str,
    start_klts: &DateTime<Utc>,
    end_klts: &DateTime<Utc>,
    klt: Klt,
    count: u16
) -> Result<Vec<Kline>> {
    let start_time = Utc::now();

    let start = 0 * count;

    let where_ =
        "WHERE security_id=$security_id AND $start_klts<=klts AND klts<=$end_klts  AND klt=$klt";
    let order_by_ = "low ASC";
    let q_str = &format!(
        "SELECT * FROM kline {} ORDER BY {} LIMIT {} START {}",
        where_,
        order_by_,
        count,
        start
    );

    let query = FUDB.query(q_str)
        .bind(("security_id", security_id))
        .bind(("start_klts", start_klts))
        .bind(("end_klts", end_klts))
        .bind(("klt", klt));

    // println!("(kline::find_lowest_klines) query = {:?}", query);

    let mut response = query.await?;

    let klines: Vec<Kline> = response.take(0)?;

    let spent_time = Local::now().signed_duration_since(start_time);
    trace!(
        "find_lowest_klines  spent seconds={}.{} ",
        spent_time.num_seconds(),
        spent_time.num_milliseconds() % 1000
    );

    Ok(klines)
}

/// select a security & its klines
///
/// security_id: &str, e.g. SZ.KZZ.128136
///
/// pi: u16, page index, start from 0
///
/// pn: u16, page size,
///
/// 必须要创建index
///
/// test/test> info for table kline
///
/// test/test> DEFINE INDEX idx_kline_security_id ON TABLE kline COLUMNS security_id; // this is very important

pub async fn select(
    security_id: &str,
    after_last_updated: Option<String>,
    klt: Option<&Klt>,
    order_by: Option<&str>,
    pi: u16,
    pn: u16
) -> Result<Vec<Kline>> {
    // 必须要创建index  DEFINE INDEX idx_kline_security_id ON TABLE kline COLUMNS security_id; // this is very important

    let where_ = match (&klt, &after_last_updated) {
        (None, None) => "WHERE security_id=$security_id",
        (None, Some(_)) => "WHERE security_id=$security_id AND last_updated>$after_last_updated",
        (Some(_), None) => "WHERE security_id=$security_id AND klt=$klt",
        (Some(_), Some(_)) =>
            "WHERE security_id=$security_id AND klt=$klt AND last_updated>$after_last_updated",
    };

    let start_time = Utc::now();

    let start = pi * pn;

    let order_by_ = if let Some(ob) = order_by { ob } else { "klts ASC" };
    let q_str = &format!(
        "SELECT * FROM kline {} ORDER BY {} LIMIT {} START {}",
        where_,
        order_by_,
        pn,
        start
    );

    // Fetch kline data within the specified date range using bind variables
    let mut query = FUDB.query(q_str);

    query = query.bind(("security_id", security_id));
    if let Some(t_) = &after_last_updated {
        query = query.bind(("after_last_updated", t_));
    }

    if let Some(t_) = &klt {
        query = query.bind(("klt", t_));
    }

    trace!("(kline::select) query={:?}", query);
    let mut response = query.await?;

    // println!("(kline::select2) result = {:?}", response);
    let klines: Vec<Kline> = response.take(0)?;
    let end_time = Utc::now();
    // (kline::select) @2024-06-24T00:29:49.068139Z spent time = TimeDelta { secs: 2, nanos: 670045000 } for klines.len=861
    // 必须要创建index  DEFINE INDEX idx_kline_security_id ON TABLE kline COLUMNS security_id;
    // 否则要花70多秒才能查找到
    // test/test> info for table kline;
    // [{ events: {  }, fields: {  }, indexes: { idx_kline_klts: 'DEFINE INDEX idx_kline_klts ON kline FIELDS klts', idx_kline_security_id: 'DEFINE INDEX idx_kline_security_id ON kline FIELDS security_id', idx_kline_security_id_klt_klts: 'DEFINE INDEX idx_kline_security_id_klt_klts ON kline FIELDS security_id, klt, klts UNIQUE', idx_kline_security_klt: 'DEFINE INDEX idx_kline_security_klt ON kline FIELDS security_id, klt' }, lives: {  }, tables: {  } }]

    trace!(
        "\n(kline::select) @{:?} spent time = {:?} for klines.len={}",
        end_time,
        end_time - start_time,
        klines.len()
    );
    Ok(klines)
}

/// select a security & its klines
///
/// security_id: &str, e.g. SZ.KZZ.128136
///
/// pi: u16, page index, start from 0
///
/// pn: u16, page size,
///
pub async fn select_kline_flats(
    security_id: &str,
    klt: &Klt,
    pi: u16,
    pn: u16
) -> Result<Vec<KlineFlat>> {
    let klines = select(security_id, None, Some(klt), Some("klts DESC"), pi, pn).await?;

    let kline_vec = klines
        .into_iter()
        .map(|kline| {
            // TODO: update select query to return all indicators
            let kline_indicator = KlineIndicator {
                ..Default::default()
            };
            // TODO: update select query to return all malines
            let kline_maline = KlineMaline {
                ..Default::default()
            };
            let kline_flat = KlineFlat::from((&kline, &kline_indicator, &kline_maline));
            kline_flat
        })
        .collect::<Vec<_>>();

    Ok(kline_vec)
}

/// select klines summary
///
/// security_id: &str, e.g. SZ.KZZ.128136
///
/// pi: u16, page index, start from 0
///
/// pn: u16, page size,
///
pub async fn select_summary(
    klts: Option<DateTime<Utc>>,
    market: Option<Market>,
    security_type: Option<SecurityType>,
    security_id: Option<&str>,
    order_by: Option<&str>,
    pi: u16,
    pn: u16
) -> Result<Vec<KlineStat>> {
    let start_time = Utc::now();

    let where_ = match (&klts, &market, &security_type, &security_id) {
        (None, None, None, None) => " ",
        (None, None, None, Some(_)) => "WHERE security_id=$security_id ",
        (None, None, Some(_), None) => "WHERE security_type=$security_type  ",
        (None, None, Some(_), Some(_)) =>
            "WHERE security_type=$security_type AND security_id=$security_id ",
        (None, Some(_), None, None) => "WHERE market=$market ",
        (None, Some(_), Some(_), None) => "WHERE market=$market AND security_type=$security_type ",
        (None, Some(_), None, Some(_)) => "WHERE market=$market AND security_id=$security_id ",
        (None, Some(_), Some(_), Some(_)) =>
            "WHERE market=$market AND security_type=$security_type AND security_id=$security_id ",

        (Some(_), None, None, None) => "WHERE klts=$klts ",
        (Some(_), None, None, Some(_)) => "WHERE klts=$klts AND security_id=$security_id ",
        (Some(_), None, Some(_), Some(_)) =>
            "WHERE klts=$klts AND security_type=$security_type AND security_id=$security_id ",
        (Some(_), Some(_), None, Some(_)) =>
            "WHERE klts=$klts AND market=$market AND security_id=$security_id ",
        (Some(_), None, Some(_), None) => "WHERE klts=$klts AND security_type=$security_type ",
        (Some(_), Some(_), None, None) => "WHERE klts=$klts AND market=$market ",
        (Some(_), Some(_), Some(_), None) =>
            "WHERE klts=$klts AND market=$market AND security_type=$security_type ",
        (Some(_), Some(_), Some(_), Some(_)) =>
            "WHERE klts=$klts AND market=$market AND security_type=$security_type AND security_id=$security_id ",
    };

    let start = pi * pn;

    let order_by_ = if let Some(ob) = order_by { ob } else { "klts ASC" };
    let q_str = &format!(
        "SELECT * FROM kline_stat {} ORDER BY {} LIMIT {} START {}",
        where_,
        order_by_,
        pn,
        start
    );

    // Fetch kline data within the specified date range using bind variables
    let mut query = FUDB.query(q_str);

    if let Some(t_) = &klts {
        query = query.bind(("klts", t_));
    }
    if let Some(t_) = &market {
        query = query.bind(("market", t_));
    }
    if let Some(t_) = &security_type {
        query = query.bind(("security_type", t_));
    }
    if let Some(t_) = &security_id {
        query = query.bind(("security_id", t_));
    }

    let mut response = query.await?;

    // println!("(kline::select2) result = {:?}", response);
    let klsums: Vec<KlineStat> = response.take(0)?;
    let end_time = Utc::now();

    trace!(
        "\n(kline::select_summary) @{:?} spent time = {:?} for klines.len={}\n q_str={}",
        end_time,
        end_time - start_time,
        klsums.len(),
        q_str
    );
    Ok(klsums)
}

///
/// 获取趋势线K线
///
pub async fn get_trend_line_klines(
    security_id: &str,
    klt: &Klt,
    pi: u16,
    pn: u16
) -> Result<TrendLineResult> {
    let klines = select(security_id, None, Some(klt), Some("klts DESC"), pi, pn).await?;

    let ctli = CrossTrendLineIndicator::new(
        &klines[50],
        &klines[29],
        (50 - 29) as f64,
        Source::Close
    );
    let ctli_inst = ctli.init(&klines[klines.len() - 1]).unwrap();

    println!("ctli_inst: {:?} klines.len={}", ctli_inst, klines.len());
    let tl_klines = ctli_inst.get_straight_trend_line(&klines).unwrap();

    Ok(TrendLineResult {
        klines: Some(tl_klines),
        trend_line_name: "tlname".to_string(),
    })
}

pub async fn select_correlation_matrix(
    security_id: &str,
    klt: &Klt,
    pi: u16,
    pn: u16
) -> Result<CorrelationMatrix> {
    let kfrs = select_kline_flats(security_id, klt, pi, pn).await?;

    let indicators = extract_indicator_item_names(&KLINE_FLAT_TABLE)?;

    let matrix = compute_indicator_item_correlation_matrix(&kfrs, &indicators)?;
    Ok(matrix)
}

pub async fn get_indicator_combinations(
    security_id: &str,
    klt: &Klt,
    comb_num: u16,
    limit: u16
) -> Result<Vec<IndicatorComb>> {
    println!(
        "(get_indicators_combinations) security_id: {} klt: {} comb_num: {} limit: {}",
        security_id,
        klt,
        comb_num,
        limit
    );
    ensure!(0 < comb_num && comb_num <= 4, "0< comb_num && comb_num <= 4 must be true");
    let idc_combs;

    if comb_num == 1 {
        idc_combs = convert_indicator_group_keys_to_combs();
    } else {
        let cr_matrix = select_correlation_matrix(security_id, klt, 0, limit).await?;

        idc_combs = cr_matrix.find_min_max_abs_value_combinations(
            &INDICATOR_GROUP_KEYS,
            comb_num as usize,
            limit as usize
        );
    }

    Ok(idc_combs)
}

fn convert_indicator_group_keys_to_combs() -> Vec<IndicatorComb> {
    let mut idc_combs = Vec::new();
    for idgk in INDICATOR_GROUP_KEYS.iter() {
        let idc_comb = IndicatorComb {
            gks: vec![*idgk],
            cell_value: 0.0,
        };
        idc_combs.push(idc_comb);
    }
    idc_combs
}

pub async fn refresh_kline_with_ticks(security_id: &str, klt: &Klt) -> Result<()> {
    info!("refresh_kline_with_ticks({}) start...", klt);
    let latest_kline: Option<Kline> = find_latest_kline(
        security_id, // "SH.KZZ.110052",
        klt
    ).await?;
    let latest_klts = match &latest_kline {
        Some(kline) => {
            let klts = kline.klts.clone();
            klts
        }
        None => { dateparser::parse("2000-01-01 00:00:00").unwrap_or_default() }
    };
    let ticks: Vec<Tick> = super::tick::select(
        security_id, //  "SH.KZZ.110052",
        Some(latest_klts),
        Some("klts ASC"),
        0,
        10000 // 5* 3600 /3 = 6000, 一天最多不超过6000条ticks
    ).await?;

    let klines = model::kline::compute_klines_from_ticks(&ticks, &latest_kline, klt);
    let rs = super::kline::upsert_all(&klines).await;

    Ok(())
}

#[cfg(test)]
mod test {
    use crate::db::{ self, FUDB };
    use crate::model::kline::Kline;
    use crate::model::kline::Klt;
    use crate::model::kline::{ self };
    use crate::model::security;
    use chrono::{ DateTime, Duration, TimeDelta };
    use chrono::Utc;
    use chrono::Local;
    use crate::db::kline::find_max_last_updated;
    use std::str::FromStr;

    use crate::model::kline::test::tick_data;

    #[tokio::test]
    async fn test_upsert_klines() {
        let _ = db::init().await.unwrap();
        for s in 0..1 {
            let symbol = &format!("symbol{s}");
            // SH.OPF.symbol0
            let security = security::Security {
                market: security::Market::SH,
                type_: security::SecurityType::OPF,
                symbol: symbol.to_owned(),
                ..Default::default()
            };

            let security_record = db::security::upsert(&security).await.unwrap().unwrap();
            let security_id = security_record.id.id.to_raw();

            let klt = Klt::M005;
            let minute_times = 5;

            let mut kline_ids = Vec::new();
            for i in 0..1 {
                let now = Utc::now();
                let add_minutes = chrono::Duration::minutes(minute_times * i);
                let new_time = now.checked_add_signed(add_minutes).unwrap();

                let kline = kline::Kline {
                    klt,
                    klts: new_time,
                    open: 10.0,
                    high: 20.0,
                    low: 8f64,
                    close: (i as f64) + 16f64,
                    volume: 100 + (i as u64),
                    ..Default::default()
                };
                let rs = db::kline::upsert(&security_id, &kline).await;

                match rs {
                    Ok(Some(t)) => {
                        println!("Thing= {:?}", t);
                        kline_ids.push(t.id);
                    }
                    Ok(None) => println!("todo!() None"),
                    Err(err) => println!("todo!() Err {}", err),
                }
            }
        }
    }

    #[tokio::test]
    async fn test_select_klines() {
        let _ = db::init().await.unwrap();

        // test result: ok. 1 passed; 0 failed; 0 ignored; 0 measured; 207 filtered out; finished in 32.92s
        let result = db::kline::select("SH.KZZ.110052", None, Some(&Klt::M005), None, 0, 100).await;

        assert_eq!(result.is_ok(), true);

        let mut to_print_dot = true;

        if let Ok(klines) = result {
            for (idx, kline) in klines.iter().enumerate() {
                if idx < 10 || idx > klines.len() - 10 {
                    println!(
                        "[{idx}] klts: {:?} ohlcv: {:?}",
                        kline.klts.with_timezone(&Local),
                        kline.ohlcv()
                    );
                } else if to_print_dot {
                    println!("...................................");
                    to_print_dot = false;
                }
            }
        }
    }

    #[tokio::test]
    async fn test_select_klines_summary() {
        let _ = db::init().await.unwrap();

        // test result: ok. 1 passed; 0 failed; 0 ignored; 0 measured; 207 filtered out; finished in 32.92s
        let result = db::kline::select_summary(
            None,
            None,
            None,
            None,
            Some("SZ.STK.002667"),
            0,
            100
        ).await;

        assert_eq!(result.is_ok(), true);

        let mut to_print_dot = true;

        if let Ok(klines) = result {
            for (idx, klsum) in klines.iter().enumerate() {
                if idx < 10 || idx > klines.len() - 10 {
                    println!(
                        "[{idx}] klts: {:?} ohlcv: {:?}",
                        klsum.klts.with_timezone(&Local),
                        klsum.ohlcv
                    );
                } else if to_print_dot {
                    println!("...................................");
                    to_print_dot = false;
                }
            }
        }
    }

    #[tokio::test]
    async fn test_find_max_last_updated() {
        let _ = db::init().await.unwrap();

        let res = find_max_last_updated("SH.OPF.symbol0").await;
        println!("res= {:?}", res);
    }

    #[tokio::test]
    async fn test_select_kline_flats() {
        let _ = db::init().await.unwrap();
        let result = db::kline::select_kline_flats("SZ.KZZ.128136", &Klt::DAY, 0, 10).await;

        // println!("{:?}", result);
        assert_eq!(result.is_ok(), true);
        if let Ok(klines_v) = &result {
            for (index, kline) in klines_v.iter().enumerate() {
                if index > 3 {
                    break;
                }
                println!(
                    "kline[{:02 }]= {:?},{},{:.2}, {},adx_adx:{:?}",
                    index,
                    kline.id,
                    kline.klt,
                    kline.klts,
                    kline.close,
                    kline.map.get("adx_adx")
                );
            }
        }
    }

    #[tokio::test]
    async fn test_find_kline_by_id() {
        let _ = db::init().await.unwrap();

        let kline = db::kline::find("SZ.KZZ.128136.DAY.20180724").await.unwrap();

        println!("(test_find_kline_by_id) {:?}", kline);
    }

    #[tokio::test]
    async fn test_find_lowest_klines() {
        let _ = db::init().await.unwrap();
        let start_klts = Utc::now() - TimeDelta::days(3 * 365);
        let end_klts = Utc::now() - TimeDelta::days(365);

        let klines = db::kline
            ::find_lowest_klines("SH.STK.600171", &start_klts, &end_klts, Klt::WEEK, 10).await
            .unwrap();

        for (idx, kline) in klines.iter().enumerate() {
            println!("[{idx}] id: {:?}, ohlcv: {:?}", kline.id, kline.ohlcv());
        }
    }

    #[tokio::test]
    async fn test_select_performance_record_links_vs_graph_relations() {
        let _ = db::init().await.unwrap();

        // query with graph relations
        let start = Utc::now();
        let query = FUDB.query(
            "SELECT * FROM kline WHERE klt = 'DAY' AND ->(kline WHERE out = security:⟨SZ.KZZ.128136⟩)"
        );
        let mut response = query.await.unwrap();
        // println!("\nresponse= {:?}\n", response);
        let _values: Vec<Kline> = response.take(0).unwrap();
        let end = Utc::now();
        let time_graph_relations = end - start;

        // query with record links
        let start = Utc::now();
        let query = FUDB.query(
            "SELECT * FROM kline WHERE klt = 'DAY' AND security_id='SZ.KZZ.128136'"
        );
        let mut response = query.await.unwrap();
        // println!("\nresponse= {:?}\n", response);
        let _values: Vec<Kline> = response.take(0).unwrap();
        let end = Utc::now();

        let time_record_links = end - start;

        // compare performance
        // time_graph_relations:PT23.540754S - time_record_links:PT7.487071S = TimeDelta { secs: 16, nanos: 53683000 }
        println!(
            "time_graph_relations:{} - time_record_links:{} = {:?}\n",
            time_graph_relations,
            time_record_links,
            time_graph_relations - time_record_links
        );
    }

    #[tokio::test]
    async fn test_refresh_kline_with_ticks() {
        let _ = db::init().await.unwrap();

        let rs = super::refresh_kline_with_ticks("SH.KZZ.110052", &Klt::M005).await;

        let result = db::kline::select("SH.KZZ.110052", None, Some(&Klt::M005), None, 0, 100).await;
        let mut to_print_dot = true;

        if let Ok(klines) = result {
            for (idx, kline) in klines.iter().enumerate() {
                if idx < 10 || idx > klines.len() - 10 {
                    println!(
                        "[{idx}] klts: {:?} ohlcv: {:?}",
                        kline.klts.with_timezone(&Local),
                        kline.ohlcv()
                    );
                } else if to_print_dot {
                    println!("...................................");
                    to_print_dot = false;
                }
            }
        }
    }
}
